package SQLAPI;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

/**
 * This is the SQLBroker for the AnvilWeb application. This is the only class in
 * the system that establishes a connection to an SQL database. The SQL class
 * invokes instances of this class to retrieve information for the web from the
 * database.
 *
 * @author Scott Catsirelis - 000602705 SAIT Polytechnic
 */
public class SQLBroker {

    private static Connection connection = null;
    private final String username = "postgres", ip = "jdbc:postgresql://127.0.0.1:5432/anvildb", password = "password";

    /**
     * Connects to the database and sets the Connection object so it can be
     * used. Ensure that the SQLBroker.close() method is called after this
     * method is run as to not leave the connection open.
     *
     * @exception SQL Exception - thrown when there is an issue connecting to
     * database
     * @exception ClassNotFoundException - thrown when a driver is not found.
     * @return boolean flag - True if connection succeeds, false if it fails.
     */
    public boolean connect() {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your PostgreSQL JDBC Driver? "
                    + "Include in your library path!");
            return false;
        }
        try {
            connection = DriverManager.getConnection(
                    ip, username,
                    password);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    /**
     * Toggles the status column in the web_user table in the database to track
     * online and offline users. If the status = 1, it will change to 0 and vice
     * versa.
     *
     * @param userid - The Users ID # that needs a flag toggled.
     * @exception SQLException - Thrown when there is a database connection issue.
     */
    public void toggleFlag(int userid) {
        ResultSet rs = null;
        int status = 2;
        try {
            String sql = "SELECT status FROM web_user where user_id=" + userid + ";";
            PreparedStatement st = connection.prepareStatement(sql);
            rs = st.executeQuery();
            while (rs.next()) {
                status = rs.getInt(1);
            }
            if (status == 1) {
                sql = "UPDATE web_user SET status=0 WHERE user_id=" + userid + ";";
                st = connection.prepareStatement(sql);
                st.executeUpdate();
            }
            if (status == 0) {
                sql = "UPDATE web_user SET status=1 WHERE user_id=" + userid + ";";
                st = connection.prepareStatement(sql);
                st.executeUpdate();
            }
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
    }

    /**
     * Returns a ResultSet object containing all of the email addresses and
     * passwords contained in the web_user table in the database.
     *
     * @return ResultSet - All email addresses and passwords from the web_user
     * table.
     * @exception SQLException - Thrown when there is a database connection issue.
     */
    public ResultSet getWebUserLogin() {
        ResultSet rs = null;
        final String sql = "SELECT email,password FROM WEB_USER;";
        try {
            PreparedStatement p = connection.prepareStatement(sql);
            rs = p.executeQuery();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        if (rs != null) {
            return rs;
        }
        return rs;
    }

    /**
     * Returns a ResultSet object containing all of the data from the
     * Usage_Report table in the database.
     *
     * @return ResultSet - All data from the usage_report table.
     * @exception SQLException - Thrown when there is a database connection
     * issue.
     */
    public ResultSet getUsageData() {
        ResultSet rs = null;

        try {
            final String sql = "SELECT * FROM USAGE_REPORT;";
            PreparedStatement st = connection.prepareStatement(sql);
            rs = st.executeQuery();

        } catch (Exception ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        if (rs != null) {
            return rs;
        }
        return rs;
    }

    /**
     * Returns a boolean after inserting a new UsageReport in to the database,
     * true for a successful insertion and false for a bad insertion.
     *
     * @param empid Integer - UserID of the web user.
     * @param message String - Message that will be logged with the report that
     * describes why it was logged.
     * @return boolean - True if insertion was successful, false if not.
     * @exception SQLException - Thrown when there is a database connection
     * issue.
     */
    public boolean insertUsageData(int empid, String message) {
        try {
            Date date = new Date();
            String usage=date.toGMTString();
            PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO usage_report(user_id, time, message) VALUES(?,?,?)");
            ps.setInt(1, empid);
            ps.setString(2,usage);
            ps.setString(3,message);
            ps.execute();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
            return false;
        }
        return true;
    }

    /**
     * Returns a ResultSet object containing all of the data from the
     * Transaction_Report table in the database.
     *
     * @return ResultSet - Contains all of the data from TRANSACTION_REPORT.
     */
    public ResultSet getTransactionData() {
        ResultSet rs = null;
        final String sql = "SELECT * FROM TRANSACTION;";
        rs = executeQuery(sql);
        return rs;
    }

    /**
     * Returns a ResultSet object containing all of the data from the Web_User
     * table in the database.
     *
     * @return ResultSet - Contains all of the data from WEB_USER.
     */
    public ResultSet getAllWebUsers() {
        ResultSet rs = null;
        final String sql = "SELECT * FROM WEB_USER;";
        rs = executeQuery(sql);
       
        return rs;
    }

    /**
     * Returns an Integer which is the users User_id in the web_user table.
     *
     * @param email String - The email of the user you wish to fetch a userID
     * for.
     * @return Integer - The userID for the supplied email address.
     * @exception SQLException - Thrown when there is a database connection
     * issue.
     */
    public int getUserId(String email) {
        ResultSet rs = null;
        int empid = 0;
        try {
            final String sql = "SELECT user_id FROM WEB_USER where email='" + email + "';";
            PreparedStatement st = connection.prepareStatement(sql);
            rs = st.executeQuery();
            while (rs.next()) {
                empid = rs.getInt(1);
            }
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }

        return empid;
    }

    /**
     * Returns a ResultSet containing the results from any SQL query submitted.
     *
     * @param query String - SQL Query you wish to execute
     * @return ResultSet - Contains results from your query.
     * @exception SQLException - Thrown when there is a database connection
     * issue.
     */
    public ResultSet executeQuery(String query) {
        ResultSet rs = null;
        try {
            PreparedStatement st = connection.prepareStatement(query);
            rs = st.executeQuery();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return rs;
    }

    /**
     * Inserts in to the TRANSACTION table in the SQL Database when an LDAP change
     * is made through the web interface. 
     * @param userid- ID # of the user that made the change.
     * @param objName - The name of the object that was modified.
     * @param type - The type of modification made. ADD, MODIFY or REMOVE.
     */
    public void insertTransaction(int userid, String objName, String type)
    {
        Date date = new Date();
        String time = date.toGMTString();
        ResultSet rs = null;
        try {
            PreparedStatement ps = connection.prepareStatement("SELECT max(usage_id)"
                    + "FROM usage_report WHERE user_id="+userid);
            
            rs=ps.executeQuery();
            rs.next();
            int usageid=rs.getInt(1);
            
            ps=connection.prepareStatement(""
                    + "INSERT INTO TRANSACTION(usage_id,user_id,change_time,object_name,transaction_type)"
                    + "VALUES (?,?,?,?,?)");
            ps.setInt(1, usageid);
            ps.setInt(2,userid);
            ps.setString(3,time);
            ps.setString(4, objName);
            ps.setString(5,type);
            ps.execute();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
    }
    
    /**
     * This method is called when an ArrayList of ChangeObjects need to be inserted
     * in to the database. 
     * @param userid - int - the user_id of the employee that made the changes.
     * @param col - An ArrayList object that contains ChangeObject objects.
     * @param objName - String - The name of the object being modified.
     */
    public void insertTransactionChanges(int userid, ArrayList<ChangeObject> col,String objName)
    {
        String type = "MODIFY";
        Date date = new Date();
        String time = date.toGMTString();
        
        ResultSet rs = null;
        try {
            PreparedStatement ps = connection.prepareStatement("SELECT max(usage_id)"
                    + "FROM usage_report WHERE user_id="+userid);
            
            rs=ps.executeQuery();
            rs.next();
            int usageid=rs.getInt(1);
            
            ps=connection.prepareStatement(""
                    + "INSERT INTO TRANSACTION(usage_id,user_id,change_time,object_name,transaction_type)"
                    + "VALUES (?,?,?,?,?)");
            ps.setInt(1, usageid);
            ps.setInt(2,userid);
            ps.setString(3,time);
            ps.setString(4, objName);
            ps.setString(5,type);
            ps.execute();
            
            ps=connection.prepareStatement("SELECT max(transaction_id)"
                    + "FROM transaction WHERE usage_id="+usageid);
            rs=ps.executeQuery();
            rs.next();
            int transid=rs.getInt(1);
            
            ps=connection.prepareStatement(""
                    + "INSERT INTO transaction_details VALUES(?,?,?,?,?)");
            
            ps.setInt(1, transid);
            ps.setString(2,objName);
            
            for(int i=0;i<col.size();i++)
            {
                ChangeObject co = col.get(i);
                ps.setString(3, co.attribute_name);
                ps.setString(4, co.previous_value);
                ps.setString(5, co.new_value);
                ps.execute();
            }
            
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        
    }
    /**
     * Returns a ResultSet from the TRANSACTION_DETAILS table based on the transactionID passed in 
     * @param transid - transaction ID of the transaction the details are needed for.
     * @return  ResultSet - Contains the transaction detail data for a specific transaction ID.
     */
    public ResultSet getTransactionDetails(int transid)
    {
        ResultSet rs = null;
        final String SQL = "SELECT * FROM TRANSACTION_DETAILS WHERE TRANSACTION_ID="+transid;
        rs=executeQuery(SQL);
        return rs;
    }
    
    /**
     * Closes the Database Connection to avoid memory leaks and save resources,
     * always remember to call this method after calling the SQLBroker.connect()
     * method.
     *
     * @exception SQLException - Thrown when there is a database connection
     * issue.
     */
    public void close() {
        try {
            connection.close();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
    }
}